Workbook Object

Creating and Opening Workbooks

If using Python xlwings, use the add method of the books object or the Book method of the xlwings package to create a workbook. Creating a new application object also creates a workbook. If using Excel VBA and Python xlwings API, use the Add method of the Workbooks object to create a workbook.

Examples

【Excel VBA】

Workbooks.Add

【Python xlwings】

>>> import xlwings as xw
>>> app = xw.App(add_book=False)
>>> bk = app.books.add()

Or use the following format:

>>> bk = xw.Book()

Or:

>>> app = xw.App()
>>> bk = app.books.active

【Python xlwings API】

>>> import xlwings as xw
>>> app = xw.App()
>>> bk = app.api.Workbooks.Add()

If using Python xlwings API, creating an application object creates a workbook, and calling Workbooks.Add creates another—effectively two workbooks. Use the following code to reference the created workbook:

>>> bk = app.api.Workbooks(1)

A newly created workbook automatically becomes the active workbook. In Python xlwings, use the active property of the books object to get the current active workbook:

>>> bk = app.books.active
>>> bk.name
'Workbook1'

In Python xlwings API, use the ActiveWorkbook object to reference the active workbook:

>>> app.api.ActiveWorkbook.Name
'Workbook1'

In Python xlwings API, you can specify the type of worksheets in a new workbook when creating it. You can specify the worksheet type directly or by referencing a file (the new workbook’s worksheets will match the type in the referenced file).

【Excel VBA】

Workbooks.Add xlWBATChart
Workbooks.Add "C:\temp.xlsx"

【Python xlwings API】

>>> bk = app.api.Workbooks.Add(xw.constants.WBATemplate.xlWBATChart)
>>> bk = app.api.Workbooks.Add(r'C:\temp.xlsx')

The worksheet type parameter has four possible values: xlWBATWorksheet, xlWBATChart, xlWBATExcel4MacroSheet, and xlWBATExcel4IntlMacroSheet, representing regular worksheets, chart worksheets, macro worksheets, and international macro worksheets, respectively.

For existing workbook files:

In Python xlwings, use the open method of the books object.

In Excel VBA and Python xlwings API, use the Open method of the Workbooks object.

If the workbook is not open, it will be opened and returned. If already open, no exception is raised—only the workbook object is returned. The open(Open) method takes a string parameter specifying the full path and filename. If only the filename is specified, the file is searched for in the current working directory.

【Excel VBA】

Workbooks.Open "C:\1.xlsx"

【Python xlwings】

>>> app.books.open(r'C:/1.xlsx')
<Book [1.xls]>
You can also open an Excel file using a Book object:
>>> xw.Book(r'C:/1.xlsx')
<Book [1.xls]>

【Python xlwings API】

>>> bk = app.api.Workbooks.Open(r'C:\1.xlsx')

Referencing, Activating, Saving, and Closing Workbooks

In Python xlwings, the book object is a member of the books object and can be referenced directly by its index in the books object:

>>> import xlwings as xw
>>> app = xw.App()
>>> app.books
<Book [Workbook1]>

You can also use parentheses for referencing (example below). Note: Square brackets use a base of 0, while parentheses use a base of 1.

>>> app.books(1)
<Book [Workbook1]>

If multiple Excel applications are open, you can reference workbooks by name. Below is an example of creating a new Excel application and referencing the workbook named "Workbook1":

>>> app = xw.App()
>>> app.books['Workbook1']

If multiple Excel applications exist, use xw.apps.keys() to get their PID indices, retrieve the desired application via the PID index, and use its books property to reference the workbook:

>>> pid = xw.apps.keys()
>>> pid
[3672, 4056]
>>> app = xw.apps[pid[0]]
>>> app.books
<Book [Workbook1]>

Activating Workbooks

Use the activate method to activate a workbook:

>>> app.books(1).activate()

Getting the Active Workbook

Use the active property of the books object to return the active workbook:

>>> app.books.active.name
'Workbook1'

Saving Workbooks

Use the save method to save a workbook:

>>> bk.save()
>>> bk.save(r'C:\path\to\new_file_name.xlsx')

Closing Workbooks

Use the close method to close a workbook without saving:

>>> bk.close()

Referencing Workbooks (Excel VBA and Python xlwings API)

In Excel VBA and Python xlwings API, you can reference workbooks by index number or name:

【Excel VBA】

Set bk = Workbooks(1)
Set bk = Workbooks("Workbook1")

【Python xlwings API】

>>> bk = app.api.Workbooks(1)
>>> bk = app.api.Workbooks('Workbook1')

Activating Workbooks (Excel VBA and Python xlwings API)

Use the Activate method to activate a workbook:

【Excel VBA】

Workbooks(1).Activate

【Python xlwings API】

>>> app.api.Workbooks(1).Activate()

Referencing the Active Workbook (Excel VBA and Python xlwings API)

Use the ActiveWorkbook object to reference the active workbook:

【Excel VBA】
ActiveWorkbook.Name ' 'Workbook1'

【Python xlwings API】

>>> app.api.ActiveWorkbook.Name
'Workbook1'

Saving Changes to Workbooks

When saving changes to a workbook:

In Excel VBA and Python xlwings API, call the Save method of the Workbook object.

In Python xlwings, call the save method of the book object.

【Excel VBA】

Set bk = Workbooks(1)
bk.Save

【Python xlwings】

>>> bk = app.books(1)
>>> bk.save()

【Python xlwings API】

>>> bk = app.api.Workbooks(1)
>>> bk.Save()

Saving As a New File

To save a file as a new file or save a newly created workbook for the first time, use the SaveAs method (parameters specify the save path and filename). If the path is omitted, the file is saved in the current directory by default. In Python xlwings, you can directly use the save method with a file path to save:

【Excel VBA】

Set bk = Workbooks(1)
bk.SaveAs "D:\test.xlsx"

【Python xlwings】

>>> bk = app.books(1)
>>> bk.save(r'D:\test.xlsx')

【Python xlwings API】

>>> bk = app.api.Workbooks(1)
>>> bk.SaveAs(r'D:\test.xlsx')

After using SaveAs to save the workbook as a new file, the original file is automatically closed, and the new file is opened. To retain the original file without opening the new file, use the SaveCopyAs method:

【Excel VBA】

Set bk = Workbooks(1)
bk.SaveCopyAs "D:\test.xlsx"

【Python xlwings API】

>>> bk = app.api.Workbooks(1)
>>> bk.SaveCopyAs(r'D:\test.xlsx')

Closing Workbooks

Use the Close(close) method of the workbook object to close workbooks. If no parameters are provided, all open workbooks are closed:

【Excel VBA】

Workbooks(1).Close

【Python xlwings】

>>> app.books(1).close()

【Python xlwings API】

>>> app.api.Workbooks(1).Close()